package com.zhentao.mapper;

import com.zhentao.pojo.TcClass;
import com.zhentao.pojo.ClassUserDTO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
* @author 86137
* @description 针对表【tc_class(班级信息表)】的数据库操作Mapper
* @createDate 2025-09-26 14:31:58
* @Entity com.zhentao.pojo.TcClass
*/
@Mapper
public interface TcClassMapper extends BaseMapper<TcClass> {

    /**
     * 根据班级ID获取班级及其用户信息
     * @param classId 班级ID
     * @return 班级用户关联信息
     */
    @Select("SELECT " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time, " +
            "u.user_id, u.openid, u.nickname, u.avatar_url, u.phone, u.identity, " +
            "u.avatar, u.token_expires, u.create_time as user_create_time, u.update_time " +
            "FROM tc_class c " +
            "LEFT JOIN tc_user u ON c.class_id = u.class_id " +
            "WHERE c.class_id = #{classId}")
    List<Map<String, Object>> getClassWithUsersRaw(@Param("classId") Integer classId);

    /**
     * 获取所有班级及其用户信息
     * @return 所有班级用户关联信息列表
     */
    @Select("SELECT " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time, " +
            "u.user_id, u.openid, u.nickname, u.avatar_url, u.phone, u.identity, " +
            "u.avatar, u.token_expires, u.create_time as user_create_time, u.update_time " +
            "FROM tc_class c " +
            "LEFT JOIN tc_user u ON c.class_id = u.class_id " +
            "ORDER BY c.create_time DESC")
    List<Map<String, Object>> getAllClassesWithUsersRaw();

    /**
     * 根据创建者ID获取班级及其用户信息
     * @param creatorUserId 创建者用户ID
     * @return 班级用户关联信息列表
     */
    @Select("SELECT " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time, " +
            "u.user_id, u.openid, u.nickname, u.avatar_url, u.phone, u.identity, " +
            "u.avatar, u.token_expires, u.create_time as user_create_time, u.update_time " +
            "FROM tc_class c " +
            "LEFT JOIN tc_user u ON c.class_id = u.class_id " +
            "WHERE c.creator_user_id = #{creatorUserId} " +
            "ORDER BY c.create_time DESC")
    List<Map<String, Object>> getMyClassesWithUsersRaw(@Param("creatorUserId") Integer creatorUserId);

    /**
     * 根据班级ID获取班级用户统计信息
     * @param classId 班级ID
     * @return 统计信息Map
     */
    @Select("SELECT " +
            "COUNT(*) as total_count, " +
            "SUM(CASE WHEN u.identity = '老师' THEN 1 ELSE 0 END) as teacher_count, " +
            "SUM(CASE WHEN u.identity = '学生' THEN 1 ELSE 0 END) as student_count, " +
            "SUM(CASE WHEN u.identity = '家长' THEN 1 ELSE 0 END) as parent_count " +
            "FROM tc_user u " +
            "WHERE u.class_id = #{classId}")
    java.util.Map<String, Object> getClassUserStatistics(@Param("classId") Integer classId);

    /**
     * 根据WxUser的classId查找TcClass班级数据
     * @param classId 班级ID
     * @return 班级信息
     */
    @Select("SELECT " +
            "class_id, class_name, class_code, creator_user_id, grade, subject, " +
            "is_headteacher, create_time " +
            "FROM tc_class " +
            "WHERE class_id = #{classId}")
    TcClass getClassByClassId(@Param("classId") Integer classId);

    /**
     * 根据WxUser的classId列表批量查找TcClass班级数据
     * @param classIds 班级ID列表
     * @return 班级信息列表
     */
    @Select("<script>" +
            "SELECT " +
            "class_id, class_name, class_code, creator_user_id, grade, subject, " +
            "is_headteacher, create_time " +
            "FROM tc_class " +
            "WHERE class_id IN " +
            "<foreach item='classId' collection='classIds' open='(' separator=',' close=')'>" +
            "#{classId}" +
            "</foreach>" +
            "</script>")
    List<TcClass> getClassesByClassIds(@Param("classIds") List<Integer> classIds);

    // ==================== 三表联查：学生-班级-教师 ====================
    
    /**
     * 三表联查：根据学生ID获取学生-班级-教师信息
     * @param studentId 学生ID
     * @return 学生班级教师关联信息
     */
    @Select("SELECT " +
            "w.id as student_id, w.openid as student_openid, w.nickname as student_name, " +
            "w.phone as student_phone, w.identity as student_identity, w.avatar_url as student_avatar, " +
            "w.create_time as student_create_time, " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time as class_create_time, " +
            "u.user_id as teacher_id, u.openid as teacher_openid, u.nickname as teacher_name, " +
            "u.phone as teacher_phone, u.identity as teacher_identity, u.avatar_url as teacher_avatar, " +
            "u.create_time as teacher_create_time " +
            "FROM wx_user w " +
            "LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "LEFT JOIN tc_user u ON c.teacher_id = u.user_id " +
            "WHERE w.id = #{studentId}")
    List<Map<String, Object>> getStudentClassTeacherByStudentId(@Param("studentId") Long studentId);

    /**
     * 三表联查：根据班级ID获取该班级所有学生及其教师信息
     * @param classId 班级ID
     * @return 班级学生教师关联信息列表
     */
    @Select("SELECT " +
            "w.id as student_id, w.openid as student_openid, w.nickname as student_name, " +
            "w.phone as student_phone, w.identity as student_identity, w.avatar_url as student_avatar, " +
            "w.create_time as student_create_time, " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time as class_create_time, " +
            "u.user_id as teacher_id, u.openid as teacher_openid, u.nickname as teacher_name, " +
            "u.phone as teacher_phone, u.identity as teacher_identity, u.avatar_url as teacher_avatar, " +
            "u.create_time as teacher_create_time " +
            "FROM wx_user w " +
            "LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "LEFT JOIN tc_user u ON c.teacher_id = u.user_id " +
            "WHERE c.class_id = #{classId}")
    List<Map<String, Object>> getStudentClassTeacherByClassId(@Param("classId") Integer classId);

    /**
     * 三表联查：根据教师ID获取该教师所教班级的所有学生信息
     * @param teacherId 教师ID
     * @return 教师班级学生关联信息列表
     */
    @Select("SELECT " +
            "w.id as student_id, w.openid as student_openid, w.nickname as student_name, " +
            "w.phone as student_phone, w.identity as student_identity, w.avatar_url as student_avatar, " +
            "w.create_time as student_create_time, " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time as class_create_time, " +
            "u.user_id as teacher_id, u.openid as teacher_openid, u.nickname as teacher_name, " +
            "u.phone as teacher_phone, u.identity as teacher_identity, u.avatar_url as teacher_avatar, " +
            "u.create_time as teacher_create_time " +
            "FROM wx_user w " +
            "LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "LEFT JOIN tc_user u ON c.teacher_id = u.user_id " +
            "WHERE u.user_id = #{teacherId}")
    List<Map<String, Object>> getStudentClassTeacherByTeacherId(@Param("teacherId") Integer teacherId);

    /**
     * 三表联查：获取所有学生-班级-教师关联信息
     * @return 所有学生班级教师关联信息列表
     */
    @Select("SELECT " +
            "w.id as student_id, w.openid as student_openid, w.nickname as student_name, " +
            "w.phone as student_phone, w.identity as student_identity, w.avatar_url as student_avatar, " +
            "w.create_time as student_create_time, " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time as class_create_time, " +
            "u.user_id as teacher_id, u.openid as teacher_openid, u.nickname as teacher_name, " +
            "u.phone as teacher_phone, u.identity as teacher_identity, u.avatar_url as teacher_avatar, " +
            "u.create_time as teacher_create_time " +
            "FROM wx_user w " +
            "LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "LEFT JOIN tc_user u ON c.teacher_id = u.user_id " +
            "ORDER BY c.create_time DESC, w.create_time DESC")
    List<Map<String, Object>> getAllStudentClassTeacher();

    /**
     * 三表联查：根据条件搜索学生-班级-教师信息
     * @param keyword 搜索关键词
     * @param grade 年级
     * @param subject 科目
     * @return 搜索结果列表
     */
    @Select("<script>" +
            "SELECT " +
            "w.id as student_id, w.openid as student_openid, w.nickname as student_name, " +
            "w.phone as student_phone, w.identity as student_identity, w.avatar_url as student_avatar, " +
            "w.create_time as student_create_time, " +
            "c.class_id, c.class_name, c.class_code, c.creator_user_id, c.grade, c.subject, " +
            "c.is_headteacher, c.create_time as class_create_time, " +
            "u.user_id as teacher_id, u.openid as teacher_openid, u.nickname as teacher_name, " +
            "u.phone as teacher_phone, u.identity as teacher_identity, u.avatar_url as teacher_avatar, " +
            "u.create_time as teacher_create_time " +
            "FROM wx_user w " +
            "LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "LEFT JOIN tc_user u ON c.teacher_id = u.user_id " +
            "WHERE 1=1 " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (w.nickname LIKE CONCAT('%', #{keyword}, '%') " +
            "OR c.class_name LIKE CONCAT('%', #{keyword}, '%') " +
            "OR u.nickname LIKE CONCAT('%', #{keyword}, '%'))" +
            "</if>" +
            "<if test='grade != null and grade != \"\"'>" +
            "AND c.grade = #{grade}" +
            "</if>" +
            "<if test='subject != null and subject != \"\"'>" +
            "AND c.subject = #{subject}" +
            "</if>" +
            "ORDER BY c.create_time DESC, w.create_time DESC" +
            "</script>")
    List<Map<String, Object>> searchStudentClassTeacher(@Param("keyword") String keyword, 
                                                         @Param("grade") String grade, 
                                                         @Param("subject") String subject);

    // ==================== 双表联查：用户-班级 ====================
    
    /**
     * 双表联查：查询所有用户及其班级信息
     * @return 所有用户及其班级信息列表
     */
    @Select("SELECT w.id, w.openid, w.nickname, w.avatar_url, w.phone, w.identity, w.create_time, w.update_time, w.status, w.class_id, " +
            "c.class_id as tc_class_id, c.class_name, c.class_code, c.grade, c.subject, c.is_headteacher, c.creator_user_id " +
            "FROM wx_user w LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "ORDER BY w.create_time DESC")
    List<Map<String, Object>> getAllUserWithClass();

    /**
     * 双表联查：查询所有学生及其班级信息
     * @return 所有学生及其班级信息列表
     */
    @Select("SELECT w.*, c.class_name, c.class_code, c.grade, c.subject, c.is_headteacher, c.creator_user_id " +
            "FROM wx_user w LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "WHERE w.identity = '学生' " +
            "ORDER BY w.create_time DESC")
    List<Map<String, Object>> getAllStudentWithClass();

    /**
     * 双表联查：根据班级ID查询该班级所有用户及其班级信息
     * @param classId 班级ID
     * @return 该班级所有用户及其班级信息列表
     */
    @Select("SELECT w.*, c.class_name, c.class_code, c.grade, c.subject, c.is_headteacher, c.creator_user_id " +
            "FROM wx_user w LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "WHERE w.class_id = #{classId} " +
            "ORDER BY w.create_time DESC")
    List<Map<String, Object>> getClassUsersWithClass(@Param("classId") Integer classId);

    /**
     * 双表联查：根据班级ID查询该班级所有学生及其班级信息
     * @param classId 班级ID
     * @return 该班级所有学生及其班级信息列表
     */
    @Select("SELECT w.*, c.class_name, c.class_code, c.grade, c.subject, c.is_headteacher, c.creator_user_id " +
            "FROM wx_user w LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "WHERE w.class_id = #{classId} AND w.identity = '学生' " +
            "ORDER BY w.create_time DESC")
    List<Map<String, Object>> getClassStudentsWithClass(@Param("classId") Integer classId);

    /**
     * 测试wx_user表数据
     * @return wx_user表数据
     */
    @Select("SELECT * FROM wx_user LIMIT 5")
    List<Map<String, Object>> testWxUserTable();

    /**
     * 测试LEFT JOIN查询
     * @return 联查结果
     */
    @Select("SELECT w.id, w.nickname, w.class_id, c.class_name " +
            "FROM wx_user w LEFT JOIN tc_class c ON w.class_id = c.class_id " +
            "LIMIT 5")
    List<Map<String, Object>> testLeftJoin();
}




